﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_PGM_GRID_Delete')
	BEGIN
		DROP Procedure usp_UPDMS_COM_PGM_GRID_Delete
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_PGM_GRID_Delete
**	Desc : 프로그램별 그리드관리을 삭제한다.
**	Test Exec Query : Exec usp_UPDMS_COM_PGM_GRID_Delete
**	Called by : Com_Dac_UPDMS_COM_PGM_GRID.cs
**	Program ID : Com1019m
**	Auth : 송시명
**	Date : 2011-02-11
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_PGM_GRID_Delete]
@ls_xml nvarchar(max) = '<UPDMS></UPDMS>' --루트노드
AS

BEGIN

DECLARE @iXmlHandle int
DECLARE @sPgmId nvarchar(20)
DECLARE @sGridId nvarchar(50)

EXEC sp_xml_preparedocument @iXmlHandle OUTPUT, @ls_xml

SET @sPgmId = ( SELECT TOP 1 Pgm_Id FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_COM_PGM_GRID', 2) WITH ( Pgm_Id nvarchar(20)) )


--UPDMS_COM_PGM_GRID_COLUMN을 먼저 삭제
DELETE FROM UPDMS_COM_PGM_GRID_COLUMN
 WHERE Pgm_Id = @sPgmId
   AND Grid_Id IN (
         SELECT Grid_Id
           FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_COM_PGM_GRID', 2)
           WITH ( Grid_Id nvarchar(50) ) x
       )


DELETE FROM UPDMS_COM_PGM_GRID
 WHERE Seq IN (
         SELECT Seq
           FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_COM_PGM_GRID', 2)
           WITH ( Seq int ) x
       )

--메모리 해제
EXEC sp_xml_removedocument @iXmlHandle

END

GO

/*
--Test Exec Query--

DECLARE @ls_xml nvarchar(max) 
SET @ls_xml='<UPDMS><UPDMS_COM_PGM_GRID><Seq>12</Seq><Pgm_Id>Com1010m</Pgm_Id><Grid_Id>GridView1</Grid_Id></UPDMS_COM_PGM_GRID><UPDMS_COM_PGM_GRID><Seq>14</Seq><Pgm_Id>Com1010m</Pgm_Id><Grid_Id>GridView3</Grid_Id></UPDMS_COM_PGM_GRID></UPDMS>'

EXEC usp_UPDMS_COM_PGM_GRID_Delete @ls_xml
*/